package com.wuseguang.springcloud.service.impl;

import com.wuseguang.springcloud.dao.jdbc.VacationJdbc;
import com.wuseguang.springcloud.service.VacationService;
import com.wuseguang.springcloud.util.CodeUtil;
import com.wuseguang.springcloud.util.PaginationUtil;
import com.wuseguang.springcloud.util.PagingTool;
import com.wuseguang.springcloud.util.Result;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.List;
import java.util.Map;

@Service
public class VacationServiceImpl implements VacationService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private VacationJdbc vacationJdbc;


    /**
     * @Author zm
     * @Description 组合查询学生信息 2.0
     * @Date 2020/8/14 9:43
     */
    @Override
    public Result queryStudentVacationList(String classIds, String name, String stime, String etime, Integer indexNo, Integer rows, Integer tSequence, Integer zSequence, Integer schoolId) {

        //state 2 通过 4 销假中 5 已销假
        String sql = "SELECT s.name className,t.student_name studentName,COUNT(1) zCount,SUM(ROUND(TIMESTAMPDIFF(SECOND,stime,etime)/3600,1)) zTime FROM vacation v,student t,classes s WHERE s.graduation=0 AND v.school_id=t.school_id AND v.student_id=t.id AND s.id=t.class_id  " +
                "AND  t.school_id=? AND v.state IN (2,4,5)  AND t.class_id IN (" + classIds + ") AND v.opertime BETWEEN ? AND ? ";

        String sql2 = "SELECT COUNT(1) zCount,IFNULL(SUM(ROUND(TIMESTAMPDIFF(SECOND,stime,etime)/3600,1)),0) zTime FROM vacation v,student t,classes s WHERE s.graduation=0 AND v.school_id=t.school_id AND v.student_id=t.id AND s.id=t.class_id  " +
                "AND  t.school_id=? AND v.state IN (2,4,5)  AND t.class_id IN (" + classIds + ") AND v.opertime BETWEEN ? AND ? ";

        if (!StringUtils.isEmpty(name)) {
            sql += "  AND t.student_name like '%" + name + "%'";
            sql2 += "  AND t.student_name like '%" + name + "%'";
        }

        sql += " GROUP BY t.id ";
        //1 正序 2倒叙
        if (tSequence == 1) {
            sql += " ORDER BY zCount ";
        }

        if (tSequence == 2) {
            sql += " ORDER BY zCount desc";
        }

        //1 正序 2倒叙
        if (zSequence == 1) {
            sql += " ORDER BY zTime ";
        }

        if (zSequence == 2) {
            sql += " ORDER BY zTime desc";
        }

        Map<String, Object> stringObjectMap = null;
        try {
            stringObjectMap = vacationJdbc.queryMap(sql2, schoolId, stime, etime);
        } catch (Exception e) {

        }


        List<Map<String, Object>> list = vacationJdbc.queryListMap(sql, schoolId, stime, etime);

        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);

        if (stringObjectMap != null) {
            PaginationUtil<?> paginationUtil = new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo);
            stringObjectMap.put("pageCount", paginationUtil.getPageCount());
            stringObjectMap.put("count", paginationUtil.getCount());
            stringObjectMap.put("rows", paginationUtil.getDataList());
            stringObjectMap.put("indexNO", paginationUtil.getIndexNO());
        }

        return new Result(stringObjectMap);
    }


    /**
     * @描述 组合查询学生请假信息情况 2.0 小程序
     * @创建人 zm
     * @创建时间 2020/3/26
     */
    @Override
    public Result queryStudentVacationAll2(Integer indexNo, Integer teacherId, Integer schoolId, Integer state, Integer rows, Integer classId) {
        //0 待审核 (1第一审核通过 2通过) 3第一审核人拒绝 4销假中 5已销假 6第二审核人未通过
        int approver = 0;//第一审批人
        String sql = " SELECT GROUP_CONCAT(c.id) string FROM  school_vacation s,teacher_role r,classes c WHERE s.one_roles_id=r.role_id  AND r.type=3 " +
                "  AND s.school_id =r.school_id AND r.school_id=? AND r.pid=c.id AND c.graduation=0 AND r.teacher_id=? ";

        if (!StringUtils.isEmpty(classId)) {
            sql += " AND c.id=" + classId;
        }

        String s = vacationJdbc.queryString(sql, schoolId, teacherId);//存在时为第一审批人

        if (StringUtils.isEmpty(s)) {//不存在时 查第二审批人
            approver = 1;//第二审批人
            sql = " SELECT GROUP_CONCAT(c.id) string FROM  school_vacation s,teacher_role r,classes c WHERE s.two_roles_id=r.role_id  AND r.type=2 " +
                    "  AND s.school_id =r.school_id AND r.school_id=?  AND r.pid=c.grade_id AND c.graduation=0 AND r.teacher_id=? ";
            if (!StringUtils.isEmpty(classId)) {
                sql += " AND c.id=" + classId;
            }
            s = vacationJdbc.queryString(sql, schoolId, teacherId);//存在时为第二审批人
        }

        List<Map<String, Object>> maps = null;
        if (!StringUtils.isEmpty(s)) {
            sql = "SELECT s.student_name name ,CONCAT('提交了',IF(type=0,'病假','事假')) title,s.head_portrait,v.id,v.student_id,v.type,v.school_id," +
                    "DATE_FORMAT(v.stime,'%Y-%m-%d %H:%i') stime,DATE_FORMAT(v.etime,'%Y-%m-%d %H:%i') etime,v.state,v.content,v.teacher_id," +
                    "DATE_FORMAT(v.opertime ,'%Y-%m-%d %H:%i') opertime ,c.name classname," + approver + " approver FROM vacation v,student s,classes c WHERE c.graduation=0 " +
                    "AND s.id=v.student_id AND c.id=s.class_id AND v.school_id=? AND s.class_id in (" + s + ") ";

            String states = state + "";

            //0 待审核 (1第一审核通过 2通过) 3第一审核人拒绝 4销假中 5已销假 6第二审核人未通过

            if (state == 0 && approver == 1) {//查询 待审核  第二审核人查看 第一审核人通过的
                states = "1";
            }


            if (state == 1 && approver == 1) {//查询 通过
                states = "2,4,5";  //2通过 4 销假中 5 已销假 均为通过状态
            }

            if (state == 2 && approver == 1) {//查询 第二审批人审核未通过
                states = "6";
            }

            if (state == 2 && approver == 0) {//查询 第一审批人审核未通过
                states = "3";
            }

            sql += " AND v.state in (" + states + ") order by opertime desc";

            maps = vacationJdbc.queryListMap(sql, schoolId);
        }

        return new Result(maps);
    }


    /**
     * @描述 组合查询学生请假信息情况
     * @创建人 zm
     * @创建时间 2020/3/26
     */
    @Override
    public Result queryStudentVacationAll(Integer indexNo, Integer classId, Integer schoolId, Integer state, Integer rows) {
        StringBuilder builder = new StringBuilder("SELECT s.student_name name ,CONCAT('提交了',IF(TYPE=0,'病假','事假')) title,s.head_portrait,v.id,v.student_id,v.type,v.school_id,DATE_FORMAT(v.stime,'%Y-%m-%d %H:%i') stime,DATE_FORMAT(v.etime,'%Y-%m-%d %H:%i') etime,v.state,v.content,v.teacher_id,DATE_FORMAT(v.opertime ,'%Y-%m-%d %H:%i') opertime ,c.name classname FROM vacation v,student s,classes c WHERE c.graduation=0 AND s.id=v.student_id AND c.id=s.class_id AND v.school_id=? ");
        if (!StringUtils.isEmpty(classId)) {
            builder.append(" AND s.class_id=").append(classId);
        }
        if (!StringUtils.isEmpty(state)) {
            builder.append(" AND v.state=").append(state);
        }

        builder.append(" ORDER BY v.opertime DESC");
        logger.info("根据班级/状态，查询学生请假信息 sql = {}", builder.toString());
        List<Map<String, Object>> maps = vacationJdbc.queryListMap(builder.toString(), schoolId);
        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(maps);
        return new Result(new PaginationUtil<>(maps.size(), rows, tool.getList(), indexNo));
    }


    /**
     * @Author zm
     * @Description 添加请假审批人
     * @Date 2020/8/14 10:39
     */
    @Override
    @Transactional
    public Result addApprover(Integer schoolId, Integer one_roles_id, Integer two_roles_id) {
        String sql = "DELETE FROM school_vacation WHERE school_id=?";
        vacationJdbc.updateByParameter(sql, schoolId);
        sql = "insert into `school_vacation` (`school_id`, `one_roles_id`, `two_roles_id`, `opertime`) values(?,?,?,now())";
        int i = vacationJdbc.updateByParameter(sql, schoolId, one_roles_id, two_roles_id);
        return i > 0 ? new Result() : new Result(CodeUtil.ADD_FAILURE);
    }


    @Override
    public Result queryApprover(Integer schoolId) {
        String sql = "SELECT one_roles_id,two_roles_id FROM school_vacation WHERE school_id=? LIMIT 1";
        try {
            Map<String, Object> stringObjectMap = vacationJdbc.queryMap(sql, schoolId);
            return new Result(stringObjectMap);
        } catch (Exception e) {

        }
        return new Result();
    }

    /**
     * @描述 修改请假状态
     * @创建人 zm
     * @创建时间 2020/3/26
     */
    @Override
    public long updateStudentVacationState(Integer id, Integer state, Integer teacherId) {
        String sql = "UPDATE vacation SET state=?,teacher_id=? WHERE id=? ";
        return vacationJdbc.updateByParameter(sql, state, teacherId, id);
    }


}
